﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using DMSMT;

namespace file
{
    class ExcelReader
    {
        public int StratRow { get; set; }
        public int StratCol { get; set; }
        public int EndRow { get; set; }
        public int SheetIndex { get; set; }
        public string SheetName { get; set; }
        public int HeadRow { get; set; }
        public List<string> HeadList { get; set; }

        public ExcelReader()
        {
            this.StratCol = 0;
            this.StratRow = 1;
            this.EndRow = 0;
            this.SheetIndex = 0;
            this.HeadRow = 1;
        }

        public static IWorkbook GetWorkBook(string path)
        {
            IWorkbook workbook = null;
            FileStream fs = null;
            string extension = Path.GetExtension(path);
            try
            {
                fs = File.OpenRead(path);
                if (extension.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else if (extension.Equals(".xlsx"))
                {
                    workbook = new XSSFWorkbook(fs);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }
            return workbook;
        }

        public ExcelReader(int col, int index)
        {
            this.StratCol = col;
            this.SheetIndex = index;
        }

        public static bool HasData(IWorkbook workbook)
        {
            if (workbook.NumberOfSheets > 0)
            {
                ISheet sheet = workbook.GetSheetAt(0);
                return sheet.PhysicalNumberOfRows > 0;
            }
            return false;
        }

        private string getCellFormatValue(ICell cell)
        {
            if (cell == null)
                return "";
            string value = cell.ToString();
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        value = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    else
                    {
                        double db = cell.NumericCellValue;
                        if (db == (int)db)
                            value = ((int)db).ToString();
                        else
                            value = db.ToString();

                        // 如果读取的是科学计数法的格式，则转换为普通格式
                        if (value != null && value.IndexOf(".") != -1 && value.IndexOf("E") != -1)
                        {
                            value = Decimal.Parse(value, System.Globalization.NumberStyles.Float).ToString();
                        }
                    }
                    break;
                case CellType.Blank:
                    value = "";
                    break;
                case CellType.Boolean:
                    value = cell.BooleanCellValue.ToString();
                    break;
                case CellType.Formula:
                    value = "Formula";
                    break;
                case CellType.Error:
                    value = "Error";
                    break;
                case CellType.String:
                    value = cell.StringCellValue;
                    break;
                default:
                    value = "";
                    break;
            }
            return value;
        }

        public List<List<string>> read(string path)
        {
            List<List<string>> list = new List<List<string>>();

            IWorkbook workbook = null;
            List<string> lRow = null;
            string value = "";
            try
            {
                workbook = GetWorkBook(path);
                ISheet sheet = workbook.GetSheetAt(SheetIndex);
                this.SheetName = sheet.SheetName;
                IRow head = sheet.GetRow(this.HeadRow);
                int cellCount = head.LastCellNum;
                this.HeadList = new List<string>();
                for (int i = this.StratCol; i < cellCount; i++)
                {
                    this.HeadList.Add(getCellFormatValue(head.GetCell(i)).Trim());
                }

                // 得到总行数
                if (this.EndRow == 0)
                    this.EndRow = sheet.LastRowNum;
                for (int i = this.StratRow - 1; i < this.EndRow; i++)
                {
                    bool is_empty = true;
                    lRow = new List<string>();
                    IRow row = sheet.GetRow(i);
                    int j = this.StratCol;
                    while (j < cellCount)
                    {
                        value = getCellFormatValue(row.GetCell(j)).Trim();
                        lRow.Add(value);
                        if (is_empty && !string.IsNullOrEmpty(value))
                            is_empty = false;
                        j++;
                    }
                    if (!is_empty)
                        list.Add(lRow);
                }
            }
            catch (Exception e)
            {
                list = new List<List<string>>();
                Log.LogError(e);
            }
            return list;
        }
    }
}
